#! /usr/bin/python
# -*- coding: cp1250 -*-

import re
import string
import sys
import glob
import os
import time
import shutil
import pepe.p_txt.bigtxt as bt
import pepe.p_datetime.stopwatches as pds
import pepe.p_dm.sdb as s
import pepe.p_oslevel.basic as osb
import pepe.p_utils.csv_pgtb2 as csvp
import pepe.p_sql.pg as pg

iso_time_now = lambda : time.strftime ("%Y-%m-%dT%H:%M:%S", time.localtime())
fw_slash = lambda x: re.sub(r'\\', '/', x)

class TxtLoad:
    """ 
    Read text delimited file and save it as postgresql table.
    Syntax: txt2pg <csv file> "^" 1
    """
    def __init__(self):
        ''' '''
        pass
    
    def initFile(self, inputFile, colDelimiter='^', hasHeader=0):
        """ initialize internal variables """
        self.debugmode = False
        path = fw_slash(osb.wai())
        self.inputFile = path + inputFile

        # pg does not see files on mapped substed disks (path must be via C disk)
        #self.replacePathDiskCR = ['R:', 'C:/AFT/root/rDisk/Clients']
        self.replacePathDiskCR = ['R:', 'C:/AFT/root/rDisk']
        self.inputFile = re.sub(  self.replacePathDiskCR[0]
                                , self.replacePathDiskCR[1]
                                , self.inputFile)

        self.inputFileNameOnly = os.path.split(self.inputFile)[1]

        # extract 'ininv' from 'ininv.dat____'
        self.tableName = string.split(self.inputFileNameOnly, '.')[0]
        # we will ignore '#' character in file name
        self.tableName = re.sub('#', '', self.tableName)

        self.colDelimiter = colDelimiter
        self.hasHeader = hasHeader
        self.hasHeaderString = ''
        self.queryCreateString = ''
        # pg does not see files on mapped substed disks (path must be via C disk)
        self.queryLoadString = ''
        self.pgClientEncoding = 'win1250'
        #if os.name == 'posix': self.pgClientEncoding = 'UTF-8'
        #self.pgClientEncoding = 'UTF-8'

    def queryCreateTable(self):
        """ scan file and create queryCreateString """
        if self.hasHeader == 1:
            cts = csvp.createTableString(     self.inputFile
                                            , self.tableName
                                            , colDelimiter=self.colDelimiter
                                            , hasHeader=1)
        else:
            cts = csvp.createTableString(     self.inputFile
                                            , self.tableName
                                            , colDelimiter=self.colDelimiter
                                            , hasHeader=0)
        self.queryCreateString = cts
    
    def createTable(self):
        """ """
        q = self.queryCreateString
        #print q
        pg.Pg(q, self.debugmode)

    def queryLoadTable(self):
        """ create queryLoadString """
        #if self.hasHeader == 1 : self.hasHeaderString = 'CSV HEADER'
        if self.hasHeader == 1 : self.hasHeaderString = ''
        self.queryLoadString = re.sub('\n {20}', '\n', """
                    SET client_encoding TO '%s';
                    COPY %s FROM '%s' with DELIMITER '%s' %s
                    """ % (   self.pgClientEncoding
                            , self.tableName
                            , fw_slash(self.inputFile)
                            , self.colDelimiter
                            , self.hasHeaderString
                            ))

    def loadData(self):
        """ """
        pg.Pg(self.queryLoadString, self.debugmode)

HELP = """#! /usr/bin/python
# -*- coding: cp1250 -*-

import re, string, sys, glob, os, time, shutil
import pepe.p_txt.bigtxt as bt
import pepe.p_utils.txt2pg as ppt
import pepe.p_sql.pg as pg

if 1: # load table
    print timenow()

    p = ppt.TxtLoad()
    p.initFile(  inputFile='%s'
               , colDelimiter='|'
               , hasHeader=1
               )

    if 1: # scan file and create string
        p.queryCreateTable()
        of = open('#c.sql', 'w'); of.write(p.queryCreateString); of.close()

    if 1: # (modify query), create table and load data
        p.queryCreateString = open('#c.sql', 'r').read()
        
        p.queryLoadTable()
        of = open('#l.sql', 'w'); of.write(p.queryLoadString); of.close()
        
        p.createTable()
        p.loadData()

    print timenow()

if 0: # select from table
    # pg.Pg('<query>')
    for r in pg.Pg('select * from <table name> limit 3'):
        print r[:3]
    # select count
    for x in pg.Pg('select count(1) from <table>'):
        print x[0]

if 0: # drop table
    pg.Pg('drop table <table name>')

if 0: # export table
    q = '''
    COPY    (select * from <table>)
            TO '%s' with DELIMITER '^'
    '''
    pg.Pg(q)
"""

if __name__ == '__main__':
    """ """
    of = open('_txt2pg.py', 'w')
    #path = fw_slash(osb.wai())
    if len(sys.argv) == 2:
        of.write(HELP % (   sys.argv[1], sys.argv[1])
                )
    else:
        of.write(HELP % ('<data file>', '<data file>')
                )
    of.close()
